package co.th.bjc.md.helper.impl;

import java.io.InputStream;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import co.th.bjc.md.dao.CustomerData;
import co.th.bjc.md.helper.entries.HelperDatabse;
import co.th.bjc.md.helper.entries.HelperDatabse.IMAGES;

public class CustomerDataHelper extends HelperDatabse{

	Logger _log = Logger.getLogger(getClass().getName());
	
	public InputStream getImgBlob(BigInteger id) throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		con = getConnection();
		stmt = con.prepareStatement("select img from customer_data where customer_data_id = ?");
		stmt.setString(1, id.toString());
		rs = stmt.executeQuery();
		InputStream in = null;
		if(rs.next()){
			in = rs.getBinaryStream("img");
		}
		rs.close();
		stmt.close();
		con.close();
		return in;
	}
	
	public List<CustomerData> getListCustomerData(String type,
			String department, int minBed, int maxBed, int presidentImpRate,
			int presidentRpRate, int directorImpRate, int directorRpRate,
			int limitFrom, int limitTo) throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		con = getConnection();
		StringBuilder condition = new StringBuilder();
		if(!type.equals("")){
			condition.append(" AND type = '"+type+"' ");
		}
		if(!department.equals("")){
			condition.append(" AND hospital_department = '"+department+"' ");
		}
		if(minBed != 0 && maxBed != 0){
			condition.append(" AND bed_capacity_min >= '"+minBed+"' AND bed_capacity_max <= '"+maxBed+"' ");
		}
		if(presidentImpRate != 0){
			condition.append(" AND president_imp_rate = '"+presidentImpRate+"' ");
		}
		if(presidentImpRate != 0){
			condition.append(" AND director_imp_rate = '"+directorImpRate+"' ");
		}
		if(presidentImpRate != 0){
			condition.append(" AND president_rp_rate = '"+presidentRpRate+"' ");
		}
		if(directorImpRate != 0){
			condition.append(" AND director_rp_rate = '"+directorRpRate+"' ");
		}
		condition.append("ORDER BY customer_data_id DESC LIMIT "+limitFrom+","+limitTo);
		stmt = con.prepareStatement(CUSTOMER_DATA.FIND_ALL+" WHERE 1=1 "+condition.toString());
		rs = stmt.executeQuery();
		List<CustomerData> list = new ArrayList<CustomerData>();
		while (rs.next()){
			CustomerData customerData = new CustomerData();
			customerData.setCustomerDataId(new BigInteger(rs.getInt("customer_data_id")+""));
			customerData.setCustomerDataCode(rs.getString("customer_data_code"));
			customerData.setBedCapacityMax(rs.getInt("bed_capacity_max"));
			customerData.setBedCapacityMin(rs.getInt("bed_capacity_min"));
			customerData.setHospitalAddress(rs.getString("hospital_address"));
			customerData.setHospitalEmail(rs.getString("hospital_email"));
			customerData.setHospitalTelephone(rs.getString("hospital_telephone"));
			customerData.setHospitalDepartment(rs.getString("hospital_department"));
			customerData.setDirectorImpRate(rs.getInt("director_imp_rate"));
			customerData.setDirectorName(rs.getString("director_name"));
			customerData.setDirectorEmail(rs.getString("director_email"));
			customerData.setDirectorTelephone(rs.getString("director_telephone"));
			customerData.setDirectorRpRate(rs.getInt("director_rp_rate"));
			customerData.setHospitalNameTh(rs.getString("hospital_name_th"));
			customerData.setHospitalNameEn(rs.getString("hospital_name_en"));
			customerData.setPresidentImpRate(rs.getInt("president_imp_rate"));
			customerData.setPresidentName(rs.getString("president_name"));
			customerData.setPresidentEmail(rs.getString("president_email"));
			customerData.setPresidentTelephone(rs.getString("president_telephone"));
			customerData.setPresidentRpRate(rs.getInt("president_rp_rate"));
			customerData.setSalePerson(rs.getString("sale_person"));
			customerData.setType(rs.getString("type"));
			customerData.setBjcBrand(rs.getString("bjc_brand"));
			customerData.setBjcCategory(rs.getString("bjc_category"));
			customerData.setBjcDepartment(rs.getString("bjc_department"));
			customerData.setBjcModel(rs.getString("bjc_model"));
			customerData.setRegion(rs.getString("region"));
			customerData.setStCompetitor(rs.getString("st_competitor"));
			customerData.setNdCompetitor(rs.getString("nd_competitor"));
			customerData.setRdCompetitor(rs.getString("rd_competitor"));
			customerData.setRemark1(rs.getString("remark1"));
			customerData.setRemark2(rs.getString("remark2"));
			list.add(customerData);
		}
		rs.close();
		stmt.close();
		con.close();
		return list;
	}
	
	public CustomerData findByCustomerDataId(BigInteger customerDataId){
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		CustomerData customerData = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement(CUSTOMER_DATA.FIND_BY_ID);
			stmt.setInt(1, customerDataId.intValue()); 
			
			rs = stmt.executeQuery();
			if (rs.next()){
				customerData = new CustomerData();
				customerData.setCustomerDataId(new BigInteger(rs.getInt("customer_data_id")+""));
				customerData.setCustomerDataCode(rs.getString("customer_data_code"));
				customerData.setBedCapacityMax(rs.getInt("bed_capacity_max"));
				customerData.setBedCapacityMin(rs.getInt("bed_capacity_min"));
				customerData.setHospitalAddress(rs.getString("hospital_address"));
				customerData.setHospitalEmail(rs.getString("hospital_email"));
				customerData.setHospitalTelephone(rs.getString("hospital_telephone"));
				customerData.setHospitalDepartment(rs.getString("hospital_department"));
				customerData.setDirectorImpRate(rs.getInt("director_imp_rate"));
				customerData.setDirectorName(rs.getString("director_name"));
				customerData.setDirectorEmail(rs.getString("director_email"));
				customerData.setDirectorTelephone(rs.getString("director_telephone"));
				customerData.setDirectorRpRate(rs.getInt("director_rp_rate"));
				customerData.setHospitalNameTh(rs.getString("hospital_name_th"));
				customerData.setHospitalNameEn(rs.getString("hospital_name_en"));
				customerData.setPresidentImpRate(rs.getInt("president_imp_rate"));
				customerData.setPresidentName(rs.getString("president_name"));
				customerData.setPresidentEmail(rs.getString("president_email"));
				customerData.setPresidentTelephone(rs.getString("president_telephone"));
				customerData.setPresidentRpRate(rs.getInt("president_rp_rate"));
				customerData.setSalePerson(rs.getString("sale_person"));
				customerData.setType(rs.getString("type"));
				customerData.setBjcBrand(rs.getString("bjc_brand"));
				customerData.setBjcCategory(rs.getString("bjc_category"));
				customerData.setBjcDepartment(rs.getString("bjc_department"));
				customerData.setBjcModel(rs.getString("bjc_model"));
				customerData.setRegion(rs.getString("region"));
				customerData.setStCompetitor(rs.getString("st_competitor"));
				customerData.setNdCompetitor(rs.getString("nd_competitor"));
				customerData.setRdCompetitor(rs.getString("rd_competitor"));
				customerData.setRemark1(rs.getString("remark1"));
				customerData.setRemark2(rs.getString("remark2"));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (rs != null) rs.close();
				if (stmt != null) stmt.close();
				if (con != null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return customerData;
	}
	
	public Integer countTotalRecords(String type,
			String department, int minBed, int maxBed, int presidentImpRate,
			int presidentRpRate, int directorImpRate, int directorRpRate){
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int count = 0;
		try {
			con = getConnection();
			StringBuilder condition = new StringBuilder();
			if(!type.equals("")){
				condition.append(" AND type = '"+type+"' ");
			}
			if(!department.equals("")){
				condition.append(" AND hospital_department = '"+department+"' ");
			}
			if(minBed != 0 && maxBed != 0){
				condition.append(" AND bed_capacity_min >= '"+minBed+"' AND bed_capacity_max <= '"+maxBed+"' ");
			}
			if(presidentImpRate != 0){
				condition.append(" AND president_imp_rate = '"+presidentImpRate+"' ");
			}
			if(presidentImpRate != 0){
				condition.append(" AND director_imp_rate = '"+directorImpRate+"' ");
			}
			if(presidentImpRate != 0){
				condition.append(" AND president_rp_rate = '"+presidentRpRate+"' ");
			}
			if(directorImpRate != 0){
				condition.append(" AND director_rp_rate = '"+directorRpRate+"' ");
			}
			stmt = con.prepareStatement("select count(*) as count_record from customer_data where 1=1 "+condition.toString());
			rs = stmt.executeQuery();
			if(rs.next()){
				count = rs.getInt("count_record");
				System.out.println("c : "+count);
			}
			return count;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if (rs != null) rs.close();
				if (stmt != null) stmt.close();
				if (con != null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count;
	}
	
	public CustomerData addCustomerData(Connection conn, CustomerData input){
		boolean isConnNull = false;
		try {
			if (conn==null){
				conn = getConnection();
				conn.setAutoCommit(true);
				isConnNull = true;
			}

			input = insert(conn, input);
		} catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			if (conn != null && isConnNull){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return input;
	}
	
	public boolean deleteCustomerData(Connection conn, BigInteger id){
		boolean isConnNull = false;
		try {
			if (conn==null){
				conn = getConnection();
				conn.setAutoCommit(true);
				isConnNull = true;
			}

			return delete(conn, id);
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}finally{
			if (conn != null && isConnNull){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	/* ===================================================================================== */
	
	private synchronized CustomerData insert(Connection conn, CustomerData input) throws SQLException{
		PreparedStatement stmt = null;
		try {						
			stmt = conn.prepareStatement(CUSTOMER_DATA.INSERT);
			
			stmt.setObject(1, null);
			stmt.setString(2, input.getCustomerDataCode());
			stmt.setInt(3, input.getBedCapacityMax());
			stmt.setInt(4, input.getBedCapacityMin());
			stmt.setString(5, input.getHospitalAddress());
			stmt.setString(6, input.getHospitalEmail());
			stmt.setString(7, input.getHospitalTelephone());
			stmt.setString(8, input.getHospitalDepartment());
			stmt.setInt(9, input.getDirectorImpRate());
			stmt.setString(10, input.getDirectorName());
			stmt.setString(11, input.getDirectorEmail());
			stmt.setString(12, input.getDirectorTelephone());
			stmt.setInt(13, input.getDirectorRpRate());
			stmt.setString(14, input.getHospitalNameTh());
			stmt.setString(15, input.getHospitalNameEn());
			stmt.setInt(16, input.getPresidentImpRate());
			stmt.setString(17, input.getPresidentName());
			stmt.setString(18, input.getPresidentEmail());
			stmt.setString(19, input.getPresidentTelephone());
			stmt.setInt(20, input.getPresidentRpRate());
			stmt.setString(21, input.getSalePerson());
			stmt.setString(22, input.getType());
			stmt.setString(23, input.getBjcBrand());
			stmt.setString(24, input.getBjcCategory());
			stmt.setString(25, input.getBjcDepartment());
			stmt.setString(26, input.getBjcModel());
			stmt.setString(27, input.getRegion());
			stmt.setString(28, input.getStCompetitor());
			stmt.setString(29, input.getNdCompetitor());
			stmt.setString(30, input.getRdCompetitor());
			stmt.setString(31, input.getRemark1());
			stmt.setString(32, input.getRemark2());
			stmt.setBlob(33, input.getImg());
			stmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (stmt != null)
				stmt.close();
		}
		return input;
	}
	
	private synchronized boolean delete(Connection conn, BigInteger customerDataId){
		PreparedStatement stmt = null;
		try {			
			stmt = conn.prepareStatement(CUSTOMER_DATA.DELETE);
			stmt.setInt(1, customerDataId.intValue());

			int record = stmt.executeUpdate();
			return record > 0 ? true : false;
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} finally {
				try {
					if (stmt != null) stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
	}
}
